<?php
class Admincp_Model_News extends Zend_Db_Table_Abstract
{
    protected $_db;

    public function __construct()
    {
        $this->_db = Zend_Registry::get('db');
    }

    public function listNews($locale)
    {
        $sql = $this->_db->select()
            ->from('news AS n')
            ->join('news_language AS nl', 'n.id = nl.news_id', array('title'))
            ->join('language AS l', 'nl.language_id = l.id', null)
            ->where('l.locale = ?', $locale)
            ->order('n.id DESC');
        return $sql;
    }

    public function getNewsById($id)
    {
        $sql = $this->_db->select()
            ->from('news')
            ->where('id = ?', $id);
        $result = $this->_db->fetchRow($sql);
        return $result;
    }

    public function getNewsLanguageById($id)
    {
        $sql = $this->_db->select()
            ->from('news_language AS nl', array('title', 'description'))
            ->join('language AS l', 'nl.language_id = l.id', array('locale'))
            ->where('nl.news_id = ?', $id);
        $result = $this->_db->fetchAll($sql);
        return $result;
    }

    public function checkNews($title, $id = null)
    {
        $sql = $this->_db->select()
            ->from('news_language')
            ->where('title = ?', $title);
        if($id != null) {
            $sql = $sql->where('news_id <> ?', $id);
        }
        $result = $this->_db->fetchAll($sql);
        return count($result);
    }

    public function addNews($data)
    {
        $this->_db->insert('news',$data);
        return $this->_db->lastInsertId();
    }

    public function addNewsLanguage($data)
    {
        $this->_db->insert('news_language',$data);
    }

    public function updateNews($data, $id)
    {
        $where = $this->getAdapter()->quoteInto('id = ?', $id);
        $this->_db->update('news', $data, $where);
    }

    public function updateNewsLanguage($data,$id)
    {
        $where = 'news_id = '.$id.' AND language_id = '.$data['language_id'];
        $this->_db->update('news_language', $data, $where);
    }

    public function getImageById($id)
    {
        $sql = $this->_db->select()
            ->from('news', array('image'))
            ->where('id = ?', $id);
        $result = $this->_db->fetchOne($sql);
        return $result;
    }

    public function deleteNews($id)
    {
        $id = (int) $id;
        $where = $this->getAdapter()->quoteInto('id = ?', $id);
        $image = $this->getImageById($id);
        if(is_file(PUBLIC_PATH . '/files/news/' . $image)) {
            @unlink(PUBLIC_PATH . '/files/news/' . $image);
        }
        $this->_db->delete('news', $where);
        $where = $this->getAdapter()->quoteInto('news_id = ?', $id);
        $this->_db->delete('news_language', $where);
    }

    public function deleteMultiNews($listId)
    {
        foreach($listId as $id) {
            $this->deleteNews($id);
        }
    }

    public function searchNews($data, $locale)
    {
        $sql = $this->_db->select()
            ->from('news AS n')
            ->join('news_language AS nl', 'n.id = nl.news_id', array('title'))
            ->join('language AS l', 'nl.language_id = l.id', null)
            ->where('l.locale = ?', $locale);
        if($data['title'] != '') {
            $sql = $sql->where('nl.title_plaintext LIKE ?', $data['title']);
        }
        if($data['status'] != 0) {
            $sql = $sql->where('n.status = ?', $data['status']);
        }
        return $sql;
    }
}
